Excel INDEX Function in Array form (Quick View):
Excel INDEX Function in Reference Form (Quick View):
Objective:
It returns a value or reference of the cell at the intersection of a particular row and column, in a given range.
Syntax of the INDEX Function in Array Form:
=INDEX (array, row_num, [column_num])
Arguments:
argumentrequired/ optionalvaluearray Required Pass a range of cells, or an array constant to this argument row_num Required Pass the row number in the cell range or the array constant col_num Optional Pass the column number in the cell range or the array constantNote:
If you use both the row_num and column_num arguments, the INDEX function will return the value from the cell at the intersection of the row_num and column_num.If you set row_num or column_num to 0 (zero), then you will get the whole column values or the whole row values in the form of arrays.Syntax of the INDEX Function in Reference Form:
=INDEX (reference, row_num, [column_num], [area_num])
Arguments:
argumentrequired/ optionalvaluereferenceRequired Pass more than one range or arrayrow_num Required Pass the row number in a specific cell rangecol_num Optional Pass the column number in a specific cell rangearea_numOptionalPass the area number that you want to select from a group of rangesNote:
If you pass more than one range or array as the array value, you should also pass the area_num.If the area_num is absent, the INDEX Function will work with the first range. If you pass a value as the area_num, the INDEX function will work in that specific range.Example 1 – Select an Item from a List
One Dimensional List with a Single Column:
To find the 3rd product in the list, use the following formula in C13, specifying the row number in C12.=INDEX(B5:B10,C12)Or,
=INDEX(B5:B10,3)
One Dimensional List with a Single Row:
To find an item in a single row using the INDEX function, specify the serial number in column B and enter the following formula in C20:=INDEX(C17:H17,,B20)Or,
=INDEX(C17:H17,3)
You can also enter the serial number directly in the formula instead of using a cell reference.
Find an Item in a Multidimensional List:
To get the item from the 3rd row and 4th column of the list, enter the following formula in C33.=INDEX(C26:H29,C31,C32)
Note:
If you specify a row number beyond the range of your list, it will cause a #REF! error.You can also refer to an array as a reference and apply the INDEX function. For example, the formula =INDEX({1,2,3;4,5,6;7,8,9;10,11,12},2,3) will return 8. The array constant {1,2,3;4,5,6;7,8,9;10,11,12} contains columns separated by semicolons.Example 2 – Selecting an Item from Multiple ListsThere are two lists in the dataset below, one for Windows and the other for MS Office.
Use the following formula to get a value in the Windows list.=INDEX((D5:G9,I5:L9),C11,E11,1)
Or,
=INDEX((D5:G9,I5:L9),C11,E11,2)to get an item in the MS Office list.
Note:
If you don’t specify the number in this formula, Excel will consider area 1 to return the value.
Example 3 – Combine the MATCH Function with the INDEX Function to Match Multiple Criteria and Return the ValueTo match criteria specified in C12 and C13.
Steps:
Enter the following formula in C14:=INDEX(B5:E10,MATCH(C13,B5:B10,0),MATCH(C12,B4:E4,0))
Formula Breakdown
MATCH(C12,B4:E4,0)Output: 3takes input from C12 and performs an exact match in B4:E4. 0 in the last argument indicates an exact match. Since the item in C12 is in the third column of B4:E4, the function returns 3.
MATCH(C13,B5:B10,0)Output: 3the function works row-wise in B5:B10, which means the items are in different rows but in one single column.
INDEX(B5:E10,MATCH(C13,B5:B10,0),MATCH(C12,B4:E4,0))Output:1930the INDEX function will check row 3 and column 3 within B5:E10 and return a value in the row-column intersection.
Example 4 – Combining the INDEX, MATCH, and IF Functions to Match Multiple Criteria from Two ListsThe dataset showcases Sales data for Windows and MS Office in different countries and years.
Steps:
The criteria are: Year: 2019, Product: MS Office, and Country: Canada.
Enter the following formula Cell C14 and press ENTER.=INDEX(INDEX((D5:G9,I5:L9),,,IF(C12="Windows",1,2)),MATCH(C13,B5:B9,0),MATCH(C11,INDEX((D5:G5,I5:L5),,,IF(C12="Windows",1,2)),0))
You will see the corresponding sales data in C14.
Make the formula more dynamic, using data validation.
Formula Breakdown
IF(C12=”Windows”,1,2))Output: 2C12 contains Windows, the criteria is not matched and the IF function returns 2.
INDEX((D5:G9,I5:L9),,,IF(C12=”Windows”,1,2))Output: {2017,2018,2019,2020;8545,8417,6318,5603;5052,8052,5137,5958;9590,6451,3177,6711;5126,3763,3317,9940}the IF(C12=”Windows”,1,2) part returns 2, so it becomes INDEX((D5:G9,I5:L9),,,2). The INDEX function returns the second range assigned to it.
MATCH(C11,INDEX((D5:G5,I5:L5),,,IF(C12=”Windows”,1,2)),0)Output: 3IF(C12=”Windows”,1,2) part returns 2, so it becomes MATCH(C11,INDEX((D5:G5,I5:L5),,,2),0). INDEX((D5:G5,I5:L5),,,2) returns I5:G5, which is {2017,2018,2019,2020}. The MATCH formula becomes MATCH(C11,{2017,2018,2019,2020},0), and returns 3 since the value 2019 in C11 is in the 3rd position of the {2017,2018,2019,2020} array.
MATCH(C13,B5:B9,0),Output: 4the MATCH function matches the value of C13 in B5:B9 range and returns 4: the position of “Canada” in B5:B9.
=INDEX({2017,2018,2019,2020;8545,8417,6318,5603;5052,8052,5137,5958;9590,6451,3177,6711;5126,3763,3317,9940},4,3)Output: 3177returns the value at the intersection the 4th row and 3rd column.
Example 5 – Returning a Row or Column Entirely from a RangeThe INDEX function returns a row or column from a range.
Steps:
To find the first row in the Windows list, enter the following formula in any cell (here, F11), and press ENTER.=INDEX(D6:G9,1,0)
Note:
In versions older than Microsoft 365, you must use the Array formula to return a row or column from a range using the INDEX Function and press CTRL + SHIFT + ENTER to enter the formula.To return an entire range, assign the range to the reference argument and enter 0 as the column and row number. The formula is:=INDEX(D6:G9,0,0)Example 6 – The INDEX Function Can Also Be Used as Cell ReferenceUse the following formula:=D6:G6INDEX(D6:G9,1,4) instead of G6 is used in the above formula:
=D6:INDEX(D6:G9,1,4)
The #REF! Error:
It occurs-
When your passed row_num argument is higher than the existing row numbers in the range.When your passed col_num argument is higher than the existing column numbers in the range.When your passed area_num argument is higher than the existing area numbers.The #VALUE! Error:
It occurs when you supply non-numeric values as row_num, col_num, or area_num.
Download Excel Workbook
Download the Excel workbook to practice.
Use of INDEX Function.xlsx
Kawser Ahmed is a Microsoft Excel Expert, Udemy Course Instructor, Data Analyst, Finance professional, and Chief Editor of ExcelDemy. He is the founder and CEO of SOFTEKO (a software and content marketing company). He has a B.Sc in Electrical and Electronics Engineering. As a Udemy instructor, he offers 8 acclaimed Excel courses, one selected for Udemy Business. A devoted MS Excel enthusiast, Kawser has contributed over 200 articles and reviewed thousands more. His expertise extends to Data Analysis,... Read Full Bio
11 Comments Reply
Baber BegJul 29, 2016 at 8:39 PMHave tried the links to download the 1200+ macros examples e-book & 100+ excel functions cheat sheet. However, it keeps asking me to reload or re-register. Please if the file sizes are not too large can you forward to my email address.
Reply
KawserAug 1, 2016 at 10:32 AMBaber,I have sent you an email with instructions. Please check. I hope the email solves the problem.Regards
Reply
AhmedJul 30, 2016 at 6:39 PMHow about adding a link to save the content for later reference?Ahmed Sheikh[email protected]
ReplyKawserAug 1, 2016 at 10:33 AMYou can do that Ahmed. Thank you.Regards
Reply
Waleed EltayebJul 31, 2016 at 4:26 PMI have many data in columns A to E , I want to find all data that corresponding to a specific data from column A . This data from A may be exist three times or more.
ReplyWaleed EltayebJul 31, 2016 at 4:30 PMI have many data in columns A to E , I want to find all data from column E that corresponding to a specific data from column A. This data from A may be exist three times or more.
ReplyKawserAug 1, 2016 at 10:34 AMWaleed,Can you upload the working files of your problems? At least a sample file? If possible send an email to this address [email protected]
Reply
Gilbert BechtolAug 24, 2016 at 6:45 AMI am trying to use the index function to display a dollar amount listed in a table in the month that it will be billed for. I have multiple projects and when the formula is dragged down to the next project, the index gets off because the projects have different start dates. Is there a better way to have the index start at the first billing month other than copying the formula from the previous project to the first billing month of the next project?
Reply
Tanjim RezaSep 4, 2022 at 3:59 PMHi, GILBERT BECHTOL!Thank you for your query.In your appeared problem, I would suggest you use the MONTH function to get individual months from each date record. Then, sort the order from smallest to largest. As a result, you’ll get the billing months of the project in sequential order and thus you can use the INDEX function to achieve your target.If your problem still doesn’t fix, please send us your Excel sheet with clearer feedback on your target in this regard.
Regards,Tanjim Reza
Reply
akshay thakkerSep 3, 2016 at 6:58 AMDear All:
I am looking for a way (without VBA) to create a dynamic array constant which has the value of {1,1,1;2,2,0;3,0,0} in column 3 and {1,1,1,1;2,2,2,0;3,3,0,0;4,0,0,0} in column 4.. and so on and so forth
This is where I have reached so far:I was able to figure out that an array formula =CHOOSE(TRANSPOSE(A1:C1),{1,1,1},{2,2,0},{3,0,0}) where A1=1,B1=2,C1=3 gives me the solution and =A1*–(A1:A3